# coding=utf-8

import traceback
import pymysql

import global_config


def singleton(cls):
    instances = {}

    def _singleton(*args, **kwargs):
        if cls not in instances:
            instances[cls] = cls(*args, **kwargs)
        return instances[cls]

    return _singleton


@singleton
class DBAction:
    # 连接池对象
    def __init__(self, database):
        self.conn = None
        self.cursor = None
        # 建立和数据库系统的连接
        self.connect()
        self.cursor.execute(f"USE {database}")
        self.databaseName = database

    def connect(self):
        self.conn = pymysql.connect(
            host=global_config.DB_HOST,
            port=3306,
            user="root",
            passwd="password"
        )
        self.cursor = self.conn.cursor()

    def test_conn(self):
        try:
            self.conn.ping(reconnect=False)
        except:
            print('reconnect')
            self.connect()
            self.cursor.execute(f"USE {self.databaseName}")

    def get_cursor(self):
        return self.cursor

    def get_db(self):
        return self.conn

    def close_database(self):
        self.cursor.close()
        self.conn.close()

    def data_operate(self, sql, params=()):
        """
        数据的插入，更新，删除
        :param database:
        :param sql:
        :return: 成功：0，失败：1
        """
        try:
            # 检查连接是否断开，如果断开就进行重连。长时间不使用就会被断开
            self.test_conn()
            self.cursor.execute(sql, params)
            self.conn.commit()
            return 0
        except:
            # print("sql is %s, params is %s error. %s" % (sql, params, traceback.format_exc()))
            print("sql is %s, params is %s error. %s" % (sql, params, traceback.print_exc()))
            self.conn.rollback()
            return -1

    def data_operate_many(self, sql, params=()):
        '''
        数据的插入，更新，删除
        :param sql:
        :param params:
        :return: 成功：0，失败：1
        '''
        # 检查连接是否断开，如果断开就进行重连
        self.test_conn()
        # 执行sql语句
        self.cursor.executemany(sql, params)
        # 提交到数据库执行
        self.conn.commit()

    def data_operate_count(self, sql, params=()):
        '''
        数据的插入，更新，删除
        :return: 受影响的条数
        '''
        # 检查连接是否断开，如果断开就进行重连
        self.test_conn()
        # 执行sql语句
        count = self.cursor.execute(sql, params)
        # 提交到数据库执行
        self.conn.commit()
        return count

    def data_inquiry(self, sql, size=10, params=()):
        '''
        :param database:
        :param sql:
        :return: ((),(),...,())
        '''
        # 检查连接是否断开，如果断开就进行重连
        self.test_conn()
        self.cursor.execute(sql, params)
        result = self.cursor.fetchmany(size)
        return result

    def data_inquiry_all(self, sql, params=()):
        '''
        :param database:
        :param sql:
        :return: ((),(),...,())
        '''
        # 检查连接是否断开，如果断开就进行重连
        self.test_conn()
        self.cursor.execute(sql, params)
        result = self.cursor.fetchall()
        return result

    def commit(self):
        self.conn.commit()
